USE [Test_Events]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetNewsArticles]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spGetNewsArticles]
GO

CREATE PROCEDURE [dbo].[spGetNewsArticles]
AS
BEGIN

DECLARE @PublicationDate DATETIME
DECLARE @CurrentDate DATETIME = GETUTCDATE()
DECLARE @days INT = -90

SET @PublicationDate = DATEADD(DAY, @days, @CurrentDate);


WITH Top20 AS (
	SELECT  dbo.NewsArticles.Id,
			dbo.NewsArticles.MainCompany_Id AS CompanyId, 
			dbo.NewsArticles.PublicationDate, 
			dbo.NewsArticles.Headline, 
			dbo.NewsArticles.ExternalUrl, 
			dbo.NewsSourceNames.Name AS Source,
			ROW_NUMBER() OVER(PARTITION BY dbo.NewsArticles.MainCompany_Id ORDER BY dbo.NewsArticles.PublicationDate DESC) AS RowNumber
    FROM   dbo.NewsArticles INNER JOIN
           dbo.NewsSourceNames ON dbo.NewsArticles.SourceNameId = dbo.NewsSourceNames.Id
    WHERE (dbo.NewsArticles.PublicationDate >= @PublicationDate)
	AND (dbo.NewsArticles.Rank = 2 OR dbo.NewsArticles.Rank = 4 OR dbo.NewsArticles.Rank = 5) 
	AND (dbo.NewsArticles.IsDiscarded = 0) 
	AND (dbo.NewsArticles.IsManuallyAdded = 0) 
	AND (dbo.NewsArticles.LexisNexis IS NULL)
)

SELECT Id, CompanyId, PublicationDate, Headline, ExternalUrl, Source FROM Top20 WHERE RowNumber <= 20

END
